package com.rivues.util.datasource;

import java.io.IOException;
import java.math.BigDecimal;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Date;
import java.util.Enumeration;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;
import java.util.Map;
import java.util.concurrent.ConcurrentMap;

import javax.servlet.http.HttpServletRequest;

import oracle.sql.TIMESTAMP;

import org.apache.commons.lang.StringUtils;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import com.rivues.core.RivuDataContext;
import com.rivues.core.RivuDataContext.DataBaseTYPEEnum;
import com.rivues.module.platform.web.handler.RequestData;
import com.rivues.module.platform.web.model.AnalyzerReportModel;
import com.rivues.module.platform.web.model.Cube;
import com.rivues.module.platform.web.model.CubeLevel;
import com.rivues.module.platform.web.model.CubeMeasure;
import com.rivues.module.platform.web.model.CubeMetadata;
import com.rivues.module.platform.web.model.Database;
import com.rivues.module.platform.web.model.Dimension;
import com.rivues.module.platform.web.model.JobDetail;
import com.rivues.module.platform.web.model.QueryLog;
import com.rivues.module.platform.web.model.QueryText;
import com.rivues.module.platform.web.model.ReportFilter;
import com.rivues.module.platform.web.model.TableProperties;
import com.rivues.module.platform.web.model.TableTask;
import com.rivues.module.platform.web.model.User;
import com.rivues.util.RivuTools;
import com.rivues.util.data.CubeData;
import com.rivues.util.data.Level;
import com.rivues.util.data.ReportData;
import com.rivues.util.data.ValueData;
import com.rivues.util.serialize.JSON;
import com.rivues.util.service.ServiceHelper;
import com.rivues.util.service.cache.CacheHelper;
import com.rivues.util.tools.ExportFile;
import com.rivues.util.tools.RivuReportDataUtil;

import freemarker.template.TemplateException;

public class SQLTools implements DataSource {
	private String CUR_SYSM_TABLE_NAME = "TB_RIVU"; 
	private final Logger log = LoggerFactory.getLogger(SQLTools.class); 
	private Database database = null ;
	private User user ;
	/**
	 * 
	 * @param database
	 */
	public SQLTools(Database database){
		this.database = database ;
	}
	
	/**
	 * 处理向下钻取
	 * @param queryText
	 * @param model
	 * @return
	 */
	public String getDrillThrough(String queryText , AnalyzerReportModel model , Cube cube){
		StringBuffer strb = new StringBuffer() , measureStr = new StringBuffer(); ;
		
		if(model!=null && cube!=null){
			
			for(Dimension dim : cube.getDimension()){
				for(CubeLevel level : dim.getCubeLevel()){
					if(measureStr.length()>0){
						measureStr.append(",") ;
					}
					measureStr.append(" ").append(level.getTablename())
					  .append(".").append(level.getRealCol(cube)).append(" ");
				}
			}
			for(CubeMeasure measure : cube.getMeasure()){
				if(measureStr.length()>0){
					measureStr.append(",") ;
				}
				measureStr.append(" ").append(measure.getTablename())
				  .append(".").append(measure.getRealCol(cube)).append(" ");
			}

		}
		if(measureStr.length()>0){
			strb.append("SELECT ").append(measureStr.toString()).append(" ").append(queryText.substring(queryText.toLowerCase().indexOf("from") ,  queryText.toLowerCase().indexOf("where"))) ;
			String wherecon = queryText.substring(queryText.toLowerCase().indexOf("where")+ 6 , queryText.toLowerCase().indexOf("order by")>0 ? queryText.toLowerCase().indexOf("order by") : queryText.length()) ;
			String ordercon = queryText.toLowerCase().indexOf("order by")>0 ? queryText.substring(queryText.toLowerCase().indexOf("order by")) : "";
			String[] cons = wherecon.split("AND|and") ;
			StringBuffer consPart = new StringBuffer() ;
			for(String con : cons){
				String[] conparts = con.split("=") ;
				if(conparts.length == 2){
					if(consPart.length()>0){
						consPart.append(" AND ") ;
					}
					consPart.append("TRIM(").append(conparts[0]).append(")").append(" = ").append(conparts[1]) ;
				}
			}	
			strb.append(" where ").append(consPart.toString()).append(" ").append(ordercon) ;
		}else{
			strb.append(queryText) ;
		}
//		RivuDataContext.getLogger(this.getClass()).info("钻取生成的SQL：" + strb.toString()) ;
		return strb.toString() ;
	}

	@Override
	public ReportData getData(AnalyzerReportModel model, Map<String , Object> requestParamValues , Cube cube,
			HttpServletRequest request, boolean parseParam , QueryText queryText , QueryLog queryLog) throws Exception {
		CubeData cubeData = new CubeData();
		java.sql.Statement statement = null;
		ResultSet result = null;
		Connection connection = null;
		try {
			replaceDataBase(model,request,parseParam);
			connection = (Connection) ServiceHelper.getDataSourceService().service(cube , null);//RivuDataContext.getConnection(cube);
			statement = connection.createStatement();
			long start = System.currentTimeMillis() ;
			cubeData.setData(new ArrayList<List<ValueData>>());
			if(request!=null && request.getParameter("total")!=null && request.getParameter("total").matches("[\\d]{1,}")){
				cubeData.setTotal(Integer.parseInt(request.getParameter("total")));
			}else{
				if(model.getExport()==null || (model.getExport()!=null&&model.getExport().getJobDetail()!=null&&model.getExport().getJobDetail().getUsearea()==null) ){
					if("true".equals(model.getCssclassname())){
						cubeData.setTotal(this.getTotal(connection, queryText , model)) ;
					}
				}
			}
			if(model.getExport()!=null&&model.getExport().getJobDetail()!=null&&model.getExport().getJobDetail().getUsearea()!=null){
				cubeData.setTotal(Integer.parseInt(model.getExport().getJobDetail().getUsearea())) ;
			}
			
			result = statement.executeQuery(queryText.getQueryText()) ;
			if(queryLog!=null){
				queryLog.setExecutetime(System.currentTimeMillis() - start) ;
			}
			start = System.currentTimeMillis() ;
			ResultSetMetaData meta = result.getMetaData();
			cubeData.setData(new ArrayList<List<ValueData>>());
			Level level = new Level("root", "col", null, 0);
			level.setChilderen(new ArrayList<Level>());
			cubeData.setCol(level);
			int colNum = meta.getColumnCount();
			for (int i = 1; i <= colNum; i++) {
				String colName = getColName(meta.getColumnName(i), model , cube);
				if(!"rownum_".equalsIgnoreCase(colName)){
					level.getChilderen().add(
						new Level(colName,
								"col", level, 1));
				}
			}


			sumRowspanColspan(level);
			level.init(); // 格式化
			ArrayList<Item> levelRowList = new ArrayList<Item>();
			RequestData data = new RequestData();
			cubeData.setRequestData(data);
			long hiveRecNum = 0 ;
			
			while (result.next()) {
				/**
				 * 处理数据导出，如果是类似HIVE的不支持分页的时候，使用这种导出模式（每批读取50000条记录，然后将50000条记录输出到流，然后在继续从ResultSet里读取）
				 */
				if(model.isIsexport() && RivuDataContext.DataBaseTYPEEnum.HIVE.toString().equalsIgnoreCase(database.getSqldialect()) && cubeData.getData().size()>=500 && model.getExport()!=null){
					//对数据进行格式化
					if(cubeData.getData()!=null && cubeData.getData().size()>0){//表格格式处理
						/**
						 * 清理 ReportData 的表头信息
						 */
						RivuReportDataUtil.cleanTitle(model , cubeData);
						/**
						 * 清理 ReportData
						 */
						RivuReportDataUtil.clearReportData(model , cubeData) ;
						
						/**
						 * 处理单元格合并
						 */
						RivuReportDataUtil.processCellMerge(model , cubeData) ;
						
						/**
						 * 优先处理 新增的行列，避免 出现 单元格操作的上海 越界
						 */
						if(model.getColformatstr()!=null && model.getColformatstr().length()>0){
							List<Object> colList = JSON.parseObject(model.getColformatstr(), List.class) ;
							if(colList!=null && colList.size()>0){
								/**
								 * 先处理所有列
								 */
								RivuReportDataUtil.processNewCol(model , cubeData , colList) ;
								
								/**
								 * 然后处理所有行
								 */
								RivuReportDataUtil.processNewRow(model , cubeData , colList , cube) ;
									
								/**
								 * 最后再处理 重命名
								 */
								//RivuReportDataUtil.processRename(model , cubeData , colList) ;
							}
						}
						
						/**
						 * 处理预警项
						 */
						//RivuReportDataUtil.processWarning(model, cubeData);
						
						/**
						 * 处理 标题描述
						 */
						//RivuReportDataUtil.processTitileDescription(cubeData , model,cube);
						/**
						 * 处理 钻取
						 */
						//reportData = RivuReportDataUtil.processDrillDown(request,cubeData , model);
						
					}
					
					model.getExport().setReportData(cubeData);
					model.getExport().createFile(false) ;
					model.getExport().setPage(model.getExport().getPage()+1);
					/**
					 * 执行完毕后将 cubedata 清空
					 */
					cubeData.getItem().clear();
					cubeData.getData().clear();
					/**
					 * 继续导
					 */
				}

				if (!model.isIsexport()&&RivuDataContext.DataBaseTYPEEnum.HIVE.toString().equalsIgnoreCase(database.getSqldialect())) {
					hiveRecNum++ ;
					if(hiveRecNum <= (queryText.getP()-1)*queryText.getPs()){
						continue ;
					}
				}

				ArrayList<ValueData> valueList = new ArrayList<ValueData>();
				for (int i = 1; i <= colNum; i++) {
					if("rownum_".equalsIgnoreCase(meta.getColumnName(i))){
						continue;
					}
					Object dataValue = result.getObject(i);
					if (dataValue == null) {
						dataValue = "";
					}
					if(dataValue != null&&dataValue instanceof TIMESTAMP){
						dataValue = TIMESTAMP.toTimestamp(((TIMESTAMP)dataValue).getBytes());
					}
					if (i == 1) {
						levelRowList.add(new Item(String.valueOf(dataValue),
								null));
					}
					valueList.add(new ValueData(dataValue, String
							.valueOf(dataValue), "", false, null, level.getChilderen().get(i-1).getName() , null ));
				}
				
				if(model.getExport()!=null&&model.getExport().getJobDetail()!=null){
					model.getExport().getJobDetail().getReport().setTotal(cubeData.getTotal());
					model.getExport().getJobDetail().getReport().getAtompages().incrementAndGet();
					model.getExport().getJobDetail().getReport().setPages(model.getExport().getJobDetail().getReport().getAtompages().intValue());
					
					model.getExport().getJobDetail().getReport().setStatus(RivuTools.status(model.getExport().getJobDetail()));
					Map<String, Object> jobDetailMap = RivuDataContext.getClusterInstance().get(RivuDataContext.DistributeEventEnum.RUNNINGJOB.toString());
					if(jobDetailMap.get(model.getExport().getJobDetail().getId())!=null){
						if(!((JobDetail)(jobDetailMap.get(model.getExport().getJobDetail().getId()))).isFetcher()){
							break ;
						}
						jobDetailMap.put(model.getExport().getJobDetail().getId(), model.getExport().getJobDetail());
					}
				}

				cubeData.getData().add(valueList);
				cubeData.setItem(levelRowList);
			}
			if(queryLog!=null){
				queryLog.setDataprocesstime(System.currentTimeMillis()-start);
			}
		} catch (Exception ex) {
			ex.printStackTrace();
			throw ex;
			
			
		} finally {
			if (result != null) {
				result.close();
			}
			if (statement != null) {
				statement.close();
			}
			if (connection != null) {
				connection.close();
			}
		}
		cubeData.setPage(queryText.getP()) ;
		cubeData.setPageSize(queryText.getPs()) ;
		cubeData.setQueryText(queryText) ;
//		cubeData.setTotal(0) ;
		if(model.getStylestr()!=null && model.getStylestr().equals("true")){
			pagetTotalProcess(cubeData , model , cube);
		}
		model.setDbType(database.getSqldialect());
		return (ReportData) cubeData;
	}
	
	@Override
	public void getRSData(AnalyzerReportModel model, Map<String , Object> requestParamValues , Cube cube,
			HttpServletRequest request, boolean parseParam , QueryText queryText ,ExportFile export) throws Exception {
		java.sql.Statement statement = null;
		ResultSet result = null;
		Connection connection = null;
		try {
			CubeData cubeData = new CubeData();
			replaceDataBase(model,request,parseParam);
			
			
			if(model.getExport()==null||(model.getExport()!=null&&model.getExport().getJobDetail()!=null&&model.getExport().getJobDetail().getUsearea()==null)){
				if("true".equals(model.getCssclassname())){
					cubeData.setTotal(this.getTotal(connection, queryText , model)) ;
				}
			}
			if(model.getExport()!=null&&model.getExport().getJobDetail()!=null&&model.getExport().getJobDetail().getUsearea()!=null){
				cubeData.setTotal(Integer.parseInt(model.getExport().getJobDetail().getUsearea())) ;
			}
			
			
			connection = (Connection) ServiceHelper.getDataSourceService().service(cube , null);//RivuDataContext.getConnection(cube);
			statement = connection.createStatement();
			cubeData.setData(new ArrayList<List<ValueData>>());
			
			
			result = statement.executeQuery(queryText.getQueryText()) ;
			ResultSetMetaData meta = result.getMetaData();
			int colNum = meta.getColumnCount();
			if(export.getReportData() == null){
				Level level = new Level("root", "col", null, 0);
				level.setChilderen(new ArrayList<Level>());
				cubeData.setCol(level);
				for (int i = 1; i <= colNum; i++) {
					String colName = getColName(meta.getColumnName(i), model , cube);
					if(!"rownum_".equalsIgnoreCase(colName)){
						level.getChilderen().add(
							new Level(colName,
									"col", level, 1));
					}
				}
				sumRowspanColspan(level);
				level.init(); // 格式化
				
				
			}
			cubeData.setData(new ArrayList<List<ValueData>>());
			List<Object> colList = null ;
			if(model.getColformatstr()!=null && model.getColformatstr().length()>0){
				colList = JSON.parseObject(model.getColformatstr(), List.class) ;
			}
			while (result.next() && RivuDataContext.getLocalRunningJob().get(export.getJobDetail().getId()).isFetcher()) {
				ArrayList<ValueData> valueList = new ArrayList<ValueData>();
				ArrayList<Item> levelRowList = new ArrayList<Item>();
				for (int i = 1; i <= colNum; i++) {
					if("rownum_".equalsIgnoreCase(meta.getColumnName(i))){
						continue;
					}
					Object dataValue = result.getObject(i);
					if (dataValue == null) {
						dataValue = "";
					}
					if(dataValue != null&&dataValue instanceof TIMESTAMP){
						dataValue = TIMESTAMP.toTimestamp(((TIMESTAMP)dataValue).getBytes());
					}
					if (i == 1) {
						levelRowList.add(new Item(String.valueOf(dataValue),
								null));
					}
					valueList.add(new ValueData(dataValue, String
							.valueOf(dataValue), "", false, null, "" , null ));
				}
				cubeData.setItem(levelRowList);
				cubeData.getData().add(valueList) ;
				/**
				 * 优先处理 新增的行列，避免 出现 单元格操作的上海 越界
				 */
				if(colList!=null && colList.size()>0){
					/**
					 * 先处理所有列
					 */
					RivuReportDataUtil.processNewCol(model , cubeData , colList) ;
					/**
					 * 然后处理所有行
					 */
//					RivuReportDataUtil.processNewRow(model , cubeData , colList , cube) ;
				}
				if(export.getReportData()==null){
					export.setReportData(cubeData);
					export.writeHead(cubeData);
				}
				/**
				 * 处理完毕
				 */
				export.writeRow(cubeData);
//				/**
//				 * 清空
//				 */
				cubeData.getData().clear(); 
				
				
				if(model.getExport()!=null&&model.getExport().getJobDetail()!=null){
					model.getExport().getJobDetail().getReport().setTotal(cubeData.getTotal());
					model.getExport().getJobDetail().getReport().getAtompages().incrementAndGet();
					model.getExport().getJobDetail().getReport().setPages(model.getExport().getJobDetail().getReport().getAtompages().intValue());
					model.getExport().getJobDetail().getReport().setStatus(RivuTools.status(model.getExport().getJobDetail()));
					
					int fetchNums = model.getExport().getJobDetail().getReport().getAtompages().intValue() ;
					
					if(fetchNums % 1000 == 0){
						Map<String, Object> jobDetailMap = RivuDataContext.getClusterInstance().get(RivuDataContext.DistributeEventEnum.RUNNINGJOB.toString());
						if(jobDetailMap.get(model.getExport().getJobDetail().getId())!=null){
							jobDetailMap.put(model.getExport().getJobDetail().getId(), model.getExport().getJobDetail());
							if(!((JobDetail)(jobDetailMap.get(model.getExport().getJobDetail().getId()))).isFetcher()){
								break ;
							}
						}
					}
				}
			}

		} catch (Exception ex) {
			ex.printStackTrace();
			throw ex;
			
		} finally {
			if (result != null) {
				result.close();
			}
			if (statement != null) {
				statement.close();
			}
			if (connection != null) {
				connection.close();
			}
		}
	}
	
	@Override
	public ReportData getVData(AnalyzerReportModel model , Map<String , Object> requestParamValues , Cube cube ,HttpServletRequest request , boolean parseParam , QueryText queryText , QueryLog queryLog) throws Exception {
		CubeData cubeData = new CubeData();
		Level root = new Level("root","col" , null , 0) ;
		root.setChilderen(new ArrayList<Level>());
		cubeData.setCol(root);
		ArrayList<ValueData> valueList = new ArrayList<ValueData>();
		/**
		 * 根据选中的维度和指标获取cubedata的表头信息，然后再填充数据
		 */
		StringBuffer consb = new StringBuffer();
		if(model.getColdimension()!=null&&model.getColdimension().length()>0){
			consb.append(model.getColdimension());
		}
		if(model.getRowdimension()!=null&&model.getRowdimension().length()>0){
			if(consb.length()>0){
				consb.append(",");
			}
			consb.append(model.getRowdimension());
		}
		 
		 String con = consb.toString();
		// 更改SQLTool生成query语句策略 如果请求参数中requesttype =self有如果是自助查询则取消分层结构
		
		Level templevel = new Level(null, "col", root, 1);
		if(con.length()>0){
			for (String cols : con.split(";")) {
				String[] rows = cols.split(",");
				for (String row : rows) {
					boolean found = false ;
					for(Dimension dim : cube.getDimension()){
						for(CubeLevel level: dim.getCubeLevel()){
							if (level.getId().equals(row)) {
								found = true ;
								templevel = new Level(level.getName(), "col", root, 1);
								templevel.setColspan(1);
								root.getChilderen().add(templevel) ;
								valueList.add(new ValueData(RivuDataContext.getVData(model.getOrgi()), RivuDataContext.getVData(model.getOrgi()), "", false, null, level.getName() , null ));
								break ;
							}
						}
						if(found) break ;
					}
				}
			}
		}
		
		for (String measures : model.getMeasure().split(",")) {
			for(CubeMeasure measure : cube.getMeasure()) {
				if (measures.equals(measure.getId())){
					templevel = new Level(measure.getName(), "col", root, 1);
					templevel.setColspan(1);
					root.getChilderen().add(templevel) ;
					valueList.add(new ValueData(RivuDataContext.getVData(model.getOrgi()), RivuDataContext.getVData(model.getOrgi()), "", false, null, measure.getName() , null ));
					break ;
				}
			}
		}
		if(con.length()==0 && (model.getMeasure()==null || model.getMeasure().length()==0)){
			for(Dimension dim : cube.getDimension()){
				for(CubeLevel level: dim.getCubeLevel()){
					templevel = new Level(level.getName(), "col", root, 1);
					templevel.setColspan(1);
					root.getChilderen().add(templevel) ;
					valueList.add(new ValueData(RivuDataContext.getVData(model.getOrgi()), RivuDataContext.getVData(model.getOrgi()), "", false, null, level.getName() , null ));
				}
			}
			for(CubeMeasure measure : cube.getMeasure()) {
				templevel = new Level(measure.getName(), "col", root, 1);
				templevel.setColspan(1);
				root.getChilderen().add(templevel) ;
				valueList.add(new ValueData(0, RivuDataContext.getVMeasureData(model.getOrgi()), "", false, null, measure.getName() , null ));
			}
		}
		cubeData.setData(new ArrayList<List<ValueData>>());
		/**
		 * 加两遍
		 */
		cubeData.getData().add(valueList) ;
		cubeData.getData().add(valueList) ;
		root.init(); 
		
		
		cubeData.setPage(queryText.getP()) ;
		cubeData.setPageSize(queryText.getPs()) ;
		cubeData.setQueryText(queryText) ;
//		cubeData.setTotal(0) ;
		if(model.getStylestr()!=null && model.getStylestr().equals("true")){
			pagetTotalProcess(cubeData , model , cube);
		}
		model.setDbType(database.getSqldialect());
		
		return cubeData ;
	}
	/**
	 * 
	 * @param cube
	 * @param level
	 * @return
	 */
	private String getTableName(Cube cube , CubeLevel level){
		String tableName = level.getTablename() ;
		for(CubeMetadata metadata: cube.getMetadata()){
			if(metadata.getTb()!=null && metadata.getTb().getTablename().equals(level.getTablename())){
				if(metadata.getTb().getTabletype().equals("2")){
					tableName = this.CUR_SYSM_TABLE_NAME ;
					break ;
				}
			}
		}
		if(cube.getCreatedata()!=null && cube.getCreatedata().equals("true")){
			tableName = cube.getTable() ;
		}
		return tableName;
	}
	/**
	 * 
	 * @param cube
	 * @param level
	 * @return
	 */
	private String getTableName(Cube cube , TableTask table){
		String tableName = table.getTablename() ;
		if(cube.getCreatedata()!=null && cube.getCreatedata().equals("true")){
			tableName = cube.getTable() ;
		}else{
			if(table.getTabletype().equals("2")){
				tableName = this.CUR_SYSM_TABLE_NAME ;
			}
		}
		return tableName;
	}
	/**
	 * 
	 * @param cube
	 * @param level
	 * @return
	 */
	private String getTableName(Cube cube , CubeMeasure measure){
		String tableName = measure.getTablename() ;
		for(CubeMetadata metadata: cube.getMetadata()){
			if(metadata.getTb()!=null && metadata.getTb().getTablename().equals(measure.getTablename())){
				if(metadata.getTb().getTabletype().equals("2")){
					tableName = this.CUR_SYSM_TABLE_NAME ;
					break ;
				}
			}
		}
		if(cube.getCreatedata()!=null && cube.getCreatedata().equals("true")){
			tableName = cube.getTable() ;
		}
		return tableName;
	}
	/**
	 * 
	 * @param connection
	 * @param queryText
	 * @return
	 * @throws SQLException
	 */
	private long getTotal(Connection connection , QueryText queryText , AnalyzerReportModel model) throws SQLException{
		long total = 0 ;
		java.sql.Statement statement = connection.createStatement();
		StringBuffer strb = new StringBuffer() ;
		strb.append("SELECT COUNT(1) R3_QUERY_RS_COUNT FROM (").append(queryText.getNativeQueryText()).append(")").append(" RIVU_COUNT ") ;
		ResultSet result = null ;
		try{
			result = statement.executeQuery(strb.toString());
			if(result.next()){
				Object value = result.getObject("R3_QUERY_RS_COUNT") ;
				if(value!=null && value.toString().matches("[\\d]{1,}")){
					total = Long.parseLong(value.toString()) ;
				}
			}
		}catch(Exception ex){
			ex.printStackTrace();
		}finally{
			log.info(model.getName() + " 查询总记录数:" + strb.toString()+" TOTAL:"+total);
			if(result!=null){
				result.close();
			}
			if(statement!=null){
				statement.close();
			}
		}
		return total ;
	}
	
	private void pagetTotalProcess(CubeData data , AnalyzerReportModel model , Cube cube){
		ArrayList<ValueData> totalValueList = new ArrayList<ValueData>() ;
		if (data.getCol().getTitle()!=null && data.getCol().getTitle().size()>0) {
			for (int i=0 ; i<data.getCol().getTitle().get(0).size(); i++) {
				Level  measurename = data.getCol().getTitle().get(0).get(i) ;
				ValueData valueData = new ValueData(measurename.getName(), null , null , "total") ;
				for(CubeMeasure measure : cube.getMeasure()){
					if(measurename.getName().equals(measure.getName())){
						if(data.getData().size()>0){
							for(int rows = 0 ; rows < data.getData().size() ; rows++){
								if(i<data.getData().get(rows).size()){
									ValueData value = data.getData().get(rows).get(i) ;
									if(value!=null && value.getValue()!=null){
										if(valueData.getValue()==null && value.getValue()!=null && value.getValue() instanceof Number){
											valueData.setValue(value.getValue()) ;
										}else{
											if("sum".equals(measure.getAggregator()) || "avg".equals(measure.getAggregator()) || "count".equals(measure.getAggregator()) || "distinct-count".equals(measure.getAggregator())){
												if(value.getValue() instanceof Integer){
													valueData.setValue(new Integer((Integer)(value.getValue()) + (Integer)(valueData.getValue()))) ;
												}else if(value.getValue() instanceof Float){
													valueData.setValue(new Float((Float)(value.getValue()) + (Float)valueData.getValue())) ;
												}else if(value.getValue() instanceof Double){
													valueData.setValue(new Double((Double)(value.getValue()) + (Double)(valueData.getValue()))) ;
												}else if(value.getValue() instanceof Number){
													valueData.setValue(new Double(((Number)(value.getValue())).doubleValue() + ((Number)(valueData.getValue())).doubleValue())) ;
												}
											}else if("max".equals(measure.getAggregator())){
												if(value.getValue() instanceof Integer && ((Integer)(value.getValue()) > (Integer)(valueData.getValue()))){
													valueData.setValue((Integer)(value.getValue())) ;
												}else if((valueData.getValue()) instanceof Float && ((Float)(value.getValue()) > (Float)(valueData.getValue()))){
													valueData.setValue((Float)(value.getValue())) ;
												}else if(value.getValue() instanceof Double && ((Double)(value.getValue()) > (Double)(valueData.getValue()))){
													valueData.setValue((Double)(value.getValue())) ;
												}else if(value.getValue() instanceof Number && (((Number)(value.getValue())).doubleValue() > ((Number)(valueData.getValue())).doubleValue())){
													valueData.setValue((Number)(value.getValue())) ;
												}
											}else if("min".equals(measure.getAggregator())){
												if(value.getValue() instanceof Integer && ((Integer)(value.getValue()) < (Integer)(valueData.getValue()))){
													valueData.setValue((Integer)(value.getValue())) ;
												}else if(value.getValue() instanceof Float && ((Float)(value.getValue()) < (Float)(valueData.getValue()))){
													valueData.setValue((Float)(value.getValue())) ;
												}else if(value.getValue() instanceof Double && ((Double)(value.getValue()) < (Double)(valueData.getValue()))){
													valueData.setValue((Double)(value.getValue())) ;
												}else if(value.getValue() instanceof Number && (((Number)(value.getValue())).doubleValue() < ((Number)(valueData.getValue())).doubleValue())){
													valueData.setValue((Number)(value.getValue())) ;
												}
											}
										}
									}
								}
							}
							
							if("avg".equals(measure.getAggregator())){
								if(valueData.getValue() instanceof Integer){
									valueData.setValue(new Integer(((Integer)(valueData.getValue()))/data.getData().size())) ;
								}else if(valueData.getValue() instanceof Float){
									valueData.setValue(new Float(((Float)(valueData.getValue()))/data.getData().size())) ;
								}else if(valueData.getValue() instanceof Double){
									valueData.setValue(new Double(((Double)(valueData.getValue()))/data.getData().size())) ;
								}else if(valueData.getValue() instanceof Number){
									valueData.setValue(new Double((((Number)(valueData.getValue())).doubleValue())/data.getData().size())) ;
								}
							}
						}
						
						
						if(valueData.getValue()!=null){
							valueData.setForamatValue(valueData.getValue().toString()) ;
						}
 						break ;
					}
				}
				totalValueList.add(valueData);
			}
		}
		if(data.getData()!=null){
			data.getData().add(totalValueList) ;
		}
	}
	private void sumRowspanColspan(Level level) {
		if (level.getChilderen() != null) {
			for (int i = 0; level.getChilderen() != null
					&& i < level.getChilderen().size(); i++) {
				Level lv = level.getChilderen().get(i);
				if (lv.getColspan() == 0 && lv.getRowspan() == 0) {
					sumRowspanColspan(lv);
				}
				if (level.getLeveltype().equals("col")) {
					level.setColspan(level.getColspan() + lv.getColspan());
				} else {
					level.setRowspan(level.getRowspan() + lv.getRowspan());
				}
			}
		} else {
			if (level.getLeveltype().equals("col")) {
				level.setColspan(1);
			} else {
				level.setRowspan(1);
			}
		}
	}

	@Override
	public void refresh(String ds, Cube cube) {
		// TODO Auto-generated method stub

	}

	@Override
	public QueryText getQuery(AnalyzerReportModel model,Cube cube ,
			HttpServletRequest request, boolean parseParam ,QueryLog queryLog) {
		QueryText query = new QueryText();
		/**
		 * 钻取
		 */
		try {
			if(request.getParameter("dt")!=null && request.getParameter("key")!=null && cube!=null && "table".equals(cube.getDstype()) && CacheHelper.getDistributedCacheBean().getCacheObject(request.getParameter("key") , null)!=null){
				ReportData data = (ReportData) CacheHelper.getDistributedCacheBean().getCacheObject(request.getParameter("key") , null) ;
				if(data!=null){
					int rowinx = request.getParameter("row") !=null && request.getParameter("row").matches("[\\d]{1,}") ? Integer.parseInt(request.getParameter("row")) : -1;
					int colinx = request.getParameter("col") !=null && request.getParameter("col").matches("[\\d]{1,}") ? Integer.parseInt(request.getParameter("col")) : -1;
					if(rowinx>=0 && colinx>=0 && data.getData().size()>rowinx){
						if(data.getData().get(rowinx).size()>colinx){
							ValueData valueData = data.getData().get(rowinx).get(colinx) ;
							query.setQueryText(this.getDrillThrough( valueData.getDrillthroughsql()  , model , cube) ) ;
							query.setDrillThrough(true) ;
						}
					}
				}
			}else{
				StringBuffer strb = new StringBuffer(), leftjoin = new StringBuffer();
				if (model.getQuerytext() != null && model.getQuerytext().length() > 0) {
					strb.append(model.getQuerytext());
					if (model.getFilterstr() != null
							&& model.getFilterstr().length() > 0) {
						if (strb.toString().toLowerCase().indexOf("where") > 0) {
							strb.append(" AND (").append(model.getFilterstr()).append(
									")");
						} else {
							strb.append(" where ").append(" (").append(
									model.getFilterstr()).append(")");
						}
					}
				} else {
					strb.append("SELECT ");
					int i = 0;
					CubeMetadata cubeMetadata = null;
					if(cube!=null && cube.getMetadata()!=null){
						for(CubeMetadata tempMetadata : cube.getMetadata()) {
							if ((tempMetadata.getMtype() == null)
									|| "0".equals(tempMetadata.getMtype())
									|| cube.getMetadata().size() == 1) {
								cubeMetadata = tempMetadata;
							}
						}
					}
		
					List<Dimension> conditionDimList = new ArrayList<Dimension>();
					if(cube!=null && cube.getDimension()!=null){
						for(Dimension dim : cube.getDimension()){
							if ("Condition".equals(dim.getType())) {
								conditionDimList.add(dim);
							}
						}
					}
					
					
					StringBuffer consb = new StringBuffer();
					if(model.getColdimension()!=null&&model.getColdimension().length()>0){
						consb.append(model.getColdimension());
					}
					if(model.getRowdimension()!=null&&model.getRowdimension().length()>0){
						if(consb.length()>0){
							consb.append(",");
						}
						consb.append(model.getRowdimension());
					}
					 
					 String con = consb.toString();
					boolean field = false;
					// 更改SQLTool生成query语句策略 如果请求参数中requesttype =self有如果是自助查询则取消分层结构
					{
						StringBuffer tempstrb = new StringBuffer();
						if(con.length()>0){
							for (String cols : con.split(";")) {
								String[] rows = cols.split(",");
								for (String row : rows) {
									for(Dimension dim : cube.getDimension()){
										Iterator<CubeLevel> leveles = dim.getCubeLevel().iterator();
										if(leveles.hasNext()){
											
											if ("Condition".equals(dim.getType())) {
												conditionDimList.add(dim);
											}
											boolean found = false;
											while (leveles.hasNext()) {
												CubeLevel level = leveles.next();
												if (level.getId().equals(row)) {
													if (tempstrb.length() > 0) {
														tempstrb.append(",");
													}
													if (level.getAttribue() != null
															&& level.getAttribue().length() > 0) {
														if(cube.getCreatedata()!=null && cube.getCreatedata().equals("true")){
															tempstrb.append(this.getTableName(cube, level)).append(
																	".").append(level.getRealCol(cube))
																	.append(" ");
														}else{
															String express = level.getAttribue() ;
															if(express.toLowerCase().indexOf("<sql>")>=0 && express.toLowerCase().indexOf("</sql>")>=0){
																express = level.getAttribue().substring(level.getAttribue().toLowerCase().indexOf("<sql>")+6, level.getAttribue().toLowerCase().indexOf("</sql>")) ;
															}
															tempstrb.append(level.getAttribue()).append(
																" AS ").append(level.getColumname())
																.append(" ");
														}
													} else {
														tempstrb.append(this.getTableName(cube, level)).append(
																".").append(level.getRealCol(cube))
																.append(" AS ").append(level.getColumname())
																.append(" ");
													}
												
//													strb.append(tempstrb);
													if (tempstrb.length() > 0) {
														field = true;
													}
													i++;
													found = true;
													break;
												}
											}
											if (found) {
												break;
											}
										}
										
									}
								}
							}
						}
						strb.append(tempstrb.toString()) ;
						boolean measureflag = false;
						StringBuffer measureStr = new StringBuffer();
						if (model != null && model.getMeasure() != null && model.getMeasure().length()>0) {
							for (String measures : model.getMeasure().split(",")) {
								StringBuilder measureids = new StringBuilder();
								for(CubeMeasure measure : cube.getMeasure()) {
									if (measures.equals(measure.getId())
											&& measureids.indexOf(measure.getId()) < 0) {
										if(measure.isCalculatedmember()){
											if (measureStr.length()>0) {
												measureStr.append(" , ");
											}
											measureStr.append(measure.getAttribue());
									        measureflag = true;
											
										}else{
											measureids.append(measure.getId());
											if (measureStr.length() > 0) {
												// measureStr.append("
												// ").append(",").append(" ") ;
												measureflag = true;
											}
											if (measureStr.length()>0) {
												measureStr.append(" , ");
											}
											measureStr.append(" ").append(this.getTableName(cube, measure))
													  .append(".").append(measure.getRealCol(cube)).append(" AS ").append(measure.getColumname())
														.append(" ");;
											measureflag = true;
										}
									}
								}
							}
						}
						if (!field && !measureflag) {
							for(Dimension dim : cube.getDimension()){
								for(CubeLevel level : dim.getCubeLevel()){
									if(measureStr.length()>0){
										measureStr.append(",") ;
									}
									measureStr.append(" ").append(this.getTableName(cube, level))
									  .append(".").append(level.getRealCol(cube)).append(" ");
								}
							}
							for(CubeMeasure measure : cube.getMeasure()){
								if(measureStr.length()>0){
									measureStr.append(",") ;
								}
								measureStr.append(" ").append(this.getTableName(cube, measure))
								  .append(".").append(measure.getRealCol(cube)).append(" ");
							}
						}
						strb.append(" ").append(con.length()>0 && tempstrb.length()>0&&measureStr.length()>0 ? ",":"").append(measureStr).append(" ");
					}
		
					strb.append(" FROM ");
					boolean hasWhereSubCon = false ;
					List<CubeMetadata> fromCon = new ArrayList<CubeMetadata>();
					if (conditionDimList.size() > 0) {
						StringBuffer whereCon = new StringBuffer();
						for (Dimension dim : conditionDimList) {
							if ("leftjoin".equals(dim.getAllmembername())) {
								for (CubeLevel level : dim.getCubeLevel()) {
									Iterator<CubeMetadata> metadata = cube
											.getMetadata().iterator();
									while (metadata.hasNext()) {
										CubeMetadata tempMetadata = metadata.next();
										for (TableProperties tp : tempMetadata.getTb()
												.getTableproperty()) {
											if (tp.getId().equals(level.getType())) {
												if (leftjoin.length() > 0) {
													leftjoin.append("  ");
												}
												leftjoin
														.append(this.getTableName(cube, level))
														.append(" left join ")
														.append(
																this.getTableName(cube, tempMetadata.getTb()))
														.append(" on ")
														.append(this.getTableName(cube, level))
														.append(".")
														.append(level.getCode())
														.append("=")
														.append(
																this.getTableName(cube, tempMetadata.getTb()))
														.append(".").append(
																tp.getFieldname());
												break;
											}
										}
									}
								}
							} else {
								for (CubeLevel level : dim.getCubeLevel()) {
									if (whereCon.length() > 0) {
										whereCon.append(" AND ");
									}
									whereCon.append(this.getTableName(cube, level)).append(".")
											.append(level.getRealCol(cube));
									Iterator<CubeMetadata> metadata = cube
											.getMetadata().iterator();
									while (metadata.hasNext()) {
										CubeMetadata tempMetadata = metadata.next();
		
										if (this.getTableName(cube, tempMetadata.getTb()).equals(
												this.getTableName(cube, level))) {
											boolean added = false;
											for (CubeMetadata cmd : fromCon) {
												if (cmd.getId().equals(
														tempMetadata.getId())) {
													added = true;
													break;
												}
											}
											if (!added) {
												fromCon.add(tempMetadata);
											}
										}
		
										for (TableProperties tp : tempMetadata.getTb()
												.getTableproperty()) {
											if (tp.getId().equals(level.getType())) {
												whereCon.append(" = ").append(
														tp.getTablename()).append(".")
														.append(tp.getName());
												{
													boolean added = false;
													for (CubeMetadata cmd : fromCon) {
														if (cmd.getId().equals(
																tempMetadata.getId())) {
															added = true;
															break;
														}
													}
													if (!added) {
														fromCon.add(tempMetadata);
													}
												}
		
												break;
											}
										}
									}
								}
							}
							break;
						}
						if (leftjoin.length() > 0) {
							strb.append(leftjoin);
						} else {
							if (fromCon.size() > 0) {
								for (int inx = 0; inx < fromCon.size(); inx++) {
									CubeMetadata cmd = fromCon.get(inx);
									if (inx > 0) {
										strb.append(" , ");
									}
									if (cmd.getTb().getTabletype().equals("2")) {
										strb.append("(").append(
												cmd.getTb().getFormatDatasql()).append(") ");
									}
									strb.append(cmd.getTb().getTablename());
								}
							}
						}
						if (whereCon.length() > 0) {
							strb.append(" WHERE ").append(whereCon);
							hasWhereSubCon = true ;
						}
					} else {
						if (cubeMetadata != null && leftjoin != null) {
							if (cubeMetadata.getTb().getTabletype().equals("2")) {
								strb.append("(").append(
										cubeMetadata.getTb().getFormatDatasql()).append(") ");
							}
							strb.append(this.getTableName(cube, cubeMetadata.getTb())) 
									.append(" ").append(leftjoin);
						}
					}
					if (model.getFilterstr() != null
							&& model.getFilterstr().length() > 0) {
						strb.append(" WHERE ").append(model.getFilterstr());
						hasWhereSubCon = true ;
					}
					/**
					 * 以下处理过滤条件
					 */
					if(model.getFilters()!=null && model.getFilters().size()>0){
						StringBuffer whereCon = new StringBuffer();
						for(ReportFilter filter : model.getFilters()){
							if(filter.getFuntype()!=null && filter.getFuntype().equals(RivuDataContext.FilteFunType.FILTER.toString())){
								boolean find = false ;
//								if((RivuTools.getDefaultValue(filter , RivuDataContext.ReportCompareEnum.COMPARE.toString())!=null && RivuTools.getDefaultValue(filter , RivuDataContext.ReportCompareEnum.COMPARE.toString()).length()>0))
								{
									
									if(filter.getContype().equals(RivuDataContext.FilterConType.DIMENSION.toString())){
										for(Dimension dim : cube.getDimension()){
											for(CubeLevel level : dim.getCubeLevel()){
												if(filter.getDataid().equals(level.getId())){
													if(RivuDataContext.ReportCompareEnum.COMPARE.toString().equals(filter.getValuefiltertype())){
														if(RivuTools.getDefaultValue(filter , RivuDataContext.ReportCompareEnum.COMPARE.toString() , request)!=null && RivuTools.getDefaultValue(filter , RivuDataContext.ReportCompareEnum.COMPARE.toString() , request).length()>0){	//支持多选 ， 其他支持多选的需要此处列出
															String values = RivuTools.getDefaultValue(filter , RivuDataContext.ReportCompareEnum.COMPARE.toString() , request) ;
															StringBuffer muli = new StringBuffer();
															for(String value : values.split(RivuDataContext.DEFAULT_VALIUE_SPLIT)){
																if(RivuDataContext.FilterCompType.EQUAL.toString().equals(filter.getComparetype()) ){
																	if(muli.length() > 0){
																		muli.append(" OR ") ;
																	}
																}else{
																	if(muli.length() > 0){
																		muli.append(" AND ") ;
																	}
																}
																
																muli.append(" ").append(filter.getFilterprefix()!=null && filter.getFilterprefix().length()>0 ? filter.getFilterprefix(): "").append(level.getRealCol(cube)).append(filter.getFiltersuffix()!=null && filter.getFiltersuffix().length()>0 ? filter.getFiltersuffix(): "").append(RivuDataContext.FilterCompType.EQUAL.toString().equals(filter.getComparetype())? "=" :"<>" ).append("'").append(value).append("' ") ;
															}
															if(whereCon.length()>0){
																whereCon.append(" AND ");
															}
															whereCon.append("(").append(muli.toString()).append(")") ;
														}else if(request.getParameter(filter.getId())!=null && request.getParameter(filter.getId()).length()>0){
															if(whereCon.length()>0){
																whereCon.append(" AND ");
															}
															whereCon.append(" ").append(filter.getFilterprefix()!=null && filter.getFilterprefix().length()>0 ? filter.getFilterprefix(): "").append(level.getRealCol(cube)).append(filter.getFiltersuffix()!=null && filter.getFiltersuffix().length()>0 ? filter.getFiltersuffix(): "").append(RivuDataContext.FilterCompType.EQUAL.toString().equals(filter.getComparetype())? "=" :"<>" ).append("'").append(request.getParameter(filter.getId())).append("' ") ;
														}else if(RivuTools.getDefaultValue(filter , RivuDataContext.ReportCompareEnum.COMPARE.toString() , request)!=null && RivuTools.getDefaultValue(filter , RivuDataContext.ReportCompareEnum.COMPARE.toString() , request).length()>0){
															String values = RivuTools.getDefaultValue(filter , RivuDataContext.ReportCompareEnum.COMPARE.toString() , request) ;
															StringBuffer tempCon = new StringBuffer();
															for(String value : values.split(RivuDataContext.DEFAULT_VALIUE_SPLIT)){
																if(RivuDataContext.FilterCompType.EQUAL.toString().equals(filter.getComparetype()) ){
																	if(tempCon.length()>0){
																		tempCon.append(" OR ") ;
																	}
																}else{
																	if(tempCon.length()>0){
																		tempCon.append(" AND ") ;
																	}
																}
																tempCon.append(" ").append(filter.getFilterprefix()!=null && filter.getFilterprefix().length()>0 ? filter.getFilterprefix(): "").append(level.getRealCol(cube)).append(filter.getFiltersuffix()!=null && filter.getFiltersuffix().length()>0 ? filter.getFiltersuffix(): "");
																if(RivuDataContext.FilterCompType.EQUAL.toString().equals(filter.getComparetype()) ){
																	tempCon.append(" = ");
																}else{
																	tempCon.append(" <> ");
																}
																tempCon.append("'").append(value).append("' ") ;
															}
															if(whereCon.length()>0){
																whereCon.append(" AND ");
															}
															whereCon.append("(").append(tempCon.toString()).append(")") ;
														}
													}else if(RivuDataContext.ReportCompareEnum.RANGE.toString().equals(filter.getValuefiltertype())){
														String start = RivuTools.getDefaultValue(filter , RivuDataContext.ReportCompareEnum.START.toString() , request) ;
														String end = RivuTools.getDefaultValue(filter , RivuDataContext.ReportCompareEnum.END.toString() , request) ;
														StringBuffer subConStr = new StringBuffer();
														if(start!=null && start.length() > 0){
															subConStr.append(" ").append(filter.getFilterprefix()!=null && filter.getFilterprefix().length()>0 ? filter.getFilterprefix(): "").append(level.getRealCol(cube)).append(filter.getFiltersuffix()!=null && filter.getFiltersuffix().length()>0 ? filter.getFiltersuffix(): "").append(RivuDataContext.FilterCompType.EQUAL.toString().equals(filter.getComparetype())? ">=" :"<" ).append("'").append(start).append("' ") ;
														}
														if(end!=null && end.length() > 0){
															if(start!=null && start.length()>0){
																if(RivuDataContext.FilterCompType.EQUAL.toString().equals(filter.getComparetype())){
																	subConStr.append(" AND ");
																}else{
																	subConStr.append(" OR ");
																}
															}
															subConStr.append(" ").append(filter.getFilterprefix()!=null && filter.getFilterprefix().length()>0 ? filter.getFilterprefix(): "").append(level.getRealCol(cube)).append(filter.getFiltersuffix()!=null && filter.getFiltersuffix().length()>0 ? filter.getFiltersuffix(): "").append(RivuDataContext.FilterCompType.EQUAL.toString().equals(filter.getComparetype())? "<=" :">" ).append("'").append(end).append("' ") ;
														}
														if(subConStr.length()>0){
															if(whereCon.length()>0){
																whereCon.append(" AND ");
															}
															whereCon.append("(").append(subConStr.toString()).append(")") ;
														}
													}
													find = true ;
													break ;
												}
											}
											if(find){
												break ;
											}
										}
									}else{
										for(CubeMeasure measure : cube.getMeasure()){
											if(filter.getDataid().equals(measure.getId())){
												if(whereCon.length()>0){
													whereCon.append(" AND ");
												}
												if(request.getParameterValues(filter.getId())!=null && request.getParameterValues(filter.getId()).length>1){	//支持多选 ， 其他支持多选的需要此处列出
													String[] values = request.getParameterValues(filter.getId()) ;
													StringBuffer muli = new StringBuffer();
													for(String value : values){
														if(muli.length() > 0){
															muli.append(" OR ") ;
														}
														muli.append(" ").append(measure.getRealCol(cube)).append("=").append(value).append(" ") ;
													}
													whereCon.append("(").append(muli.toString()).append(")") ;
												}else{
													whereCon.append(" ").append(measure.getRealCol(cube)).append("=").append(request.getParameter(filter.getId())).append(" ") ;
												}
												break  ;
											}
										}
									}
								}
							}
						}
						if(whereCon.length()>0){
							if(hasWhereSubCon){
								strb.append(" AND (").append(whereCon.toString()).append(" ) ") ; 
							}else{
								strb.append(" WHERE ").append(whereCon.toString()) ; 
							}
						}
					}
					
					if (model.getSortName() != null) {
						strb.append(" order by ").append(model.getSortstr()).append(" ").append(model.getSortType());
					}
				}
				String sql = processParam(strb.toString(), cube , model , request , true , query);
				if (sql.indexOf("$") >= 0) {
					/**
					 * 一下替换 模板
					 */
					try {
						Map<String, Object> valueMap = new HashMap<String, Object>();
						if (parseParam) {
							Enumeration<String> param = request.getParameterNames();
							while (param.hasMoreElements()) {
								String name = param.nextElement();
								if (request.getParameter(name) != null
										&& request.getParameter(name).trim().length() > 0) {
									valueMap.put(name, request.getParameter(name));
								}
							}
						}
						int p = parseParam ? Integer
								.parseInt(request.getParameter("p") != null
										&& request.getParameter("p").matches(
												"[\\d]{1,}") ? request
										.getParameter("p") : "1") : 1;
						int ps = parseParam ? Integer
								.parseInt(request.getParameter("ps") != null
										&& request.getParameter("ps").matches(
												"[\\d]{1,}") ? request
										.getParameter("ps") : "10") : 10;
						if (p < 1) {
							p = 1;
						}
						if (ps < 1) {
							ps = 10;
						}
						query.setP(p) ;
						query.setPs(ps) ;
						
						valueMap.put("p", p);
						valueMap.put("ps", ps);
						valueMap.put("startindex", (p - 1) * ps);
						valueMap.put("request", request);
						valueMap.put("model", model);
						sql = RivuTools.getTemplet(strb.toString(), valueMap);
					} catch (IOException e) {
						e.printStackTrace();
					} catch (TemplateException e) {
						e.printStackTrace();
					}
				} else{
					/**
					 * 以下保存未分页的 SQL
					 */
					
					query.setNativeQueryText(sql) ;
					
					if (model.isIsexport() && "true".equals(model.getIsloadfulldata())) {
						// do nothing
					} else {
						int p = parseParam ? Integer
								.parseInt(request.getParameter("p") != null
										&& request.getParameter("p").matches(
												"[\\d]{1,}") ? request
										.getParameter("p") : "1") : 1;
						int ps = parseParam ? Integer
								.parseInt(request.getParameter("ps") != null
										&& request.getParameter("ps").matches(
												"[\\d]{1,}") ? request
										.getParameter("ps") : "0") : 0;
						if (p < 1) {
							p = 1;
						}
						if (ps < 1) {
							ps = model.getPageSize()>0 ? model.getPageSize() : 50;
						}
						int startindex = (p - 1) * ps;
						query.setP(p) ;
						query.setPs(ps) ;
						if (RivuDataContext.DataBaseTYPEEnum.MYSQL.toString().equalsIgnoreCase(database.getSqldialect())) {
							sql = new StringBuilder(sql).append(" LIMIT ").append(
									startindex).append(",").append(ps).toString();
						}
						if (RivuDataContext.DataBaseTYPEEnum.HIVE.toString().equalsIgnoreCase(database.getSqldialect())&&!model.isIsexport()) {
							sql = new StringBuilder(sql).append(" LIMIT ").append(
									startindex+ps).toString();
						}
						if (RivuDataContext.DataBaseTYPEEnum.SQLSERVER.toString().equalsIgnoreCase(database.getSqldialect())) {
		
						}
						if(RivuDataContext.DataBaseTYPEEnum.POSTGRESQL.toString().equalsIgnoreCase(database.getSqldialect())){
							sql = new StringBuilder(sql).append(" LIMIT ").append(ps).append(" offset ").append(startindex).toString();
						}
						if (RivuDataContext.DataBaseTYPEEnum.ORACLE.toString().equalsIgnoreCase(database.getSqldialect())) {
							sql = new StringBuilder()
									.append(
											"select * from ( select row_.*, rownum rownum_ from (")
									.append(sql).append(") row_ where rownum <= ")
									.append(p * ps).append(") where rownum_ > ")
									.append(startindex).toString();
						}
					}
		
				}
				query.setQueryText(sql) ;
			}
		} catch (NumberFormatException e1) {
			e1.printStackTrace();
		} catch (Exception e1) {
			e1.printStackTrace();
		}
		log.info(model.getName() + ":" + query.getQueryText());
		return query;
	}

	public String getColName(String name, AnalyzerReportModel model , Cube cube) {
		String colAliasName = null;
		Iterator<Dimension> dimension = cube.getDimension()
				.iterator();
		Iterator<CubeMeasure> measure = cube.getMeasure().iterator();
		while (dimension.hasNext()) {
			Dimension dim = dimension.next();
			Iterator<CubeLevel> leveles = dim.getCubeLevel().iterator();
			while (leveles.hasNext()) {
				CubeLevel level = leveles.next();
				if (level.getRealCol(cube) != null
						&& level.getRealCol(cube).equalsIgnoreCase(name)) {
					colAliasName = level.getName();
					break;
				}
			}
			if (colAliasName != null) {
				break;
			}
		}
		if (colAliasName == null) {
			while (measure.hasNext()) {
				CubeMeasure dim = measure.next();
				if (dim.getCode() != null
						&& dim.getCode().equalsIgnoreCase(name)) {
					colAliasName = dim.getName();
					break;
				}
				if (colAliasName != null) {
					break;
				}
			}
		}
		return colAliasName != null ? colAliasName : name;
	}

	@Override
	public Date getDate() {
		return new Date();
	}

	/**
	 * 如果请求带有参数 则更改用户识别码 查询对应数据源
	 * @param request
	 * @param model
	 * @param parseParam
	 * @throws Exception
	 */
	private void replaceDataBase(AnalyzerReportModel model , HttpServletRequest request , boolean parseParam) throws Exception{
		
	}
	public String processParam(String value , Cube cube , AnalyzerReportModel model , HttpServletRequest request , boolean parseParam , QueryText queryText ){
		/**
		 * 一下替换 模板
		 */
		try {
			Map<String , Object> valueMap = new HashMap<String , Object>();
			valueMap.put("filters", model.getFilters());
			if(parseParam&&request.getParameterNames()!=null){
				Enumeration<String> param = request.getParameterNames() ;
				while(param.hasMoreElements()){
					String name = param.nextElement() ;
					if(request.getParameter(name)!=null && request.getParameter(name).trim().length()>0){
						valueMap.put(name, request.getParameter(name)) ;
					}
				}
			}
			int p = parseParam?Integer.parseInt(request.getParameter("p") !=null && request.getParameter("p").matches("[\\d]{1,}") ? request.getParameter("p") :"1") :1;
			int ps = parseParam?Integer.parseInt(request.getParameter("ps") !=null && request.getParameter("ps").matches("[\\d]{1,}") ? request.getParameter("ps") :"20"):20;
			if(p<1){
				p = 1;
			}
			if(ps<1){
				ps =20 ;
			}
			queryText.setP(p) ;
			queryText.setPs(ps) ;
			valueMap.put("p", p) ;
			valueMap.put("ps", ps) ;
			valueMap.put("startindex", (p-1) * ps) ;
			if(parseParam){
				valueMap.put("request",request) ;
			}
			valueMap.put("user", request.getSession(true)==null?new User():request.getSession(true).getAttribute(RivuDataContext.USER_SESSION_NAME)) ;
			valueMap.put("model", model) ;
			value = RivuTools.getTemplet(value, valueMap) ;
		} catch (IOException e) {
			e.printStackTrace();
		} catch (TemplateException e) {
			e.printStackTrace();
		}
		return value ;
	}
	@Override
	public QueryText getQuery(AnalyzerReportModel model, Cube cube ,
			HttpServletRequest request  , CubeLevel level , String con ,QueryLog queryLog , boolean useStaticFilter, ReportFilter filter) {
		QueryText query = new QueryText();
		StringBuffer strb = new StringBuffer();
		strb.append("SELECT DISTINCT ").append(level.getRealCol(cube)).append(" FROM ") ;
		for(CubeMetadata meta : cube.getMetadata()){
			if(level.getTablename()!=null && meta.getTb()!=null && level.getTablename().equals(meta.getTb().getTablename())){
				if (meta.getTb().getTabletype().equals("2")) {
					if(cube.getCreatedata()!=null && cube.getCreatedata().equals("true")){
						strb.append(this.getTableName(cube, meta.getTb()));
					}else{
						if(meta.getTb().getDatasql().indexOf("$")>=0||meta.getTb().getDatasql().indexOf("<#")>=0){
							strb.append("(").append(processParam(meta.getTb().getDatasql() , cube , model , request , true , query)).append(") ");
						}else{
							strb.append("(").append(
									meta.getTb().getDatasql()).append(") ");
						}
						
					}
				}else{
					strb.append(this.getTableName(cube, meta.getTb())) ;
				}
			}
		}
		String wherestr = RivuTools.appendFilterSqlWhere(model.getFilters(), filter, cube);
		if(!StringUtils.isBlank(wherestr)){
			strb.append(" WHERE ").append(wherestr);
		}
		strb.append(" ORDER BY ").append(level.getRealCol(cube)).append(" ASC") ;
		
		
		Object obj =  (String) CacheHelper.getDistributedDictionaryCacheBean().getCacheObject("system.config.report.filter.auto.size", model.getOrgi()) ;
		int filter_auto_size = 1000;
		if(obj!=null){
			filter_auto_size = Integer.parseInt(obj.toString());
		}
		
		if (RivuDataContext.DataBaseTYPEEnum.MYSQL.toString().equalsIgnoreCase(database.getSqldialect())) {
			strb.append(" LIMIT ").append(0).append(",").append(filter_auto_size);
		}
		if (RivuDataContext.DataBaseTYPEEnum.HIVE.toString().equalsIgnoreCase(database.getSqldialect())) {
			strb.append(" LIMIT ").append(filter_auto_size).toString();
		}
		if (RivuDataContext.DataBaseTYPEEnum.SQLSERVER.toString().equalsIgnoreCase(database.getSqldialect())) {

		}
		if(RivuDataContext.DataBaseTYPEEnum.POSTGRESQL.toString().equalsIgnoreCase(database.getSqldialect())){
			strb.append(" LIMIT ").append(filter_auto_size).append(" offset ").append(0);
		}
		if (RivuDataContext.DataBaseTYPEEnum.ORACLE.toString().equalsIgnoreCase(database.getSqldialect())) {
			strb = new StringBuffer().append(
							"select * from ( select row_.*, rownum rownum_ from (")
					.append(strb.toString()).append(") row_ where rownum <= ")
					.append(filter_auto_size).append(") where rownum_ > ")
					.append(0);
		}
		
		query.setQueryText(strb.toString()) ;
		filter.setQueryText(strb.toString());
		
		
		query.setNativeQueryText(strb.toString()) ;
		StringBuffer queryLogStr = new StringBuffer();
		/**
		 * 记录日志，先判断当前用户是否登录
		 */
		if(queryLog!=null){
			if(request!=null && request.getSession().getAttribute(RivuDataContext.USER_SESSION_NAME)!=null){
				User user = (User) request.getSession().getAttribute(RivuDataContext.USER_SESSION_NAME) ;
				queryLogStr.append(queryLog.getOrgi()).append("/").append(queryLog.getFlowid()).append("/").append(user.getId()).append("/").append(user.getUsername()).append(" ") ;
				queryLogStr.append(strb) ;
				log.info(queryLogStr.toString());
			}else{
				queryLogStr.append(queryLog.getOrgi()).append("/").append(queryLog.getFlowid()).append(" ").append(queryLogStr.toString()) ;
				log.info(queryLogStr.toString());
			}
		}
		return query;
	}
	
	public void setDatabase(Database database) {
		this.database = database ;
	}
	public Database getDatabase() {
		return database;
	}

	@Override
	public boolean valid(Cube cube) throws Exception {
		// TODO Auto-generated method stub
		return true;
	}

	public User getUser() {
		return user;
	}

	public void setUser(User user) {
		this.user = user;
	}
	
}
